# -*- coding: utf-8 -*-
from decimal import Decimal
import json
import pytz
import kpi.Kpi_business as Kpi_business

import utils.config as config
# import config as config
from sqlalchemy import create_engine, Column, Integer, String, func, and_, or_, distinct, text, desc, select,case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.exc import PendingRollbackError
from bottle import Bottle, request, response, template, static_file
import sys
import os
import pandas as pd
import time
from datetime import datetime
import datetime as datetime_do
from dateutil.relativedelta import relativedelta

Base = declarative_base()

# 创建实例 连接shadowmode库
engine = create_engine(
    f"mysql+pymysql://{config.db_username}:{config.db_password}@{config.db_host}:{config.db_port}/{config.db_database}?charset=utf8",
    max_overflow=0,
    pool_size=10,  # 连接池大小
    pool_pre_ping=True
)
SessionFactory = sessionmaker(bind=engine)
session = scoped_session(SessionFactory)


def timestamp_to_str(date_time):
    # 将时间戳转换为 UTC datetime 对象
    utc_dt = datetime.fromtimestamp(date_time, pytz.utc)
    # 转换为本地时间
    local_dt = utc_dt.astimezone()  # 默认转换为本地时区
    # 格式化输出
    formatted_time = local_dt.strftime('%Y-%m-%d %H:%M:%S')
    return formatted_time


def str2Array(string_, spilt_str=","):
    resList = []
    if string_ and len(string_) > 0:
        temp_list = string_.split(spilt_str)
        for temp in temp_list:
            if len(temp) > 0:
                resList.append(temp)
    return resList


def date_str_to_timestamp(date_str):
    return int(time.mktime(time.strptime(date_str, "%Y-%m-%d")))


kpiServiceApp = Bottle()


@kpiServiceApp.route('/ai_function_kpi', method=['post'])
def ai_function_kpi():
    """
    [POST] /ai_function_kpi 智驾KPI统计接口,统计一个时间段内的KPI数据，将一次返回整个页面的所有需要数据,开始时间须小于或等于结束时间

    Parameters
    ----------
    start_time : str
        开始时间 2025-01-01
    end_time : str
        结束时间 2025-01-20
    ota_versions :str
            ota版本号，逗号隔开  OTA6-202503150411
    car_code :str
            车型 E03/E0Y 

    Returns
    -------
    object
        JSON对象
          {
            "total_vehicles": "",
            "mileages": {  # 智驾里程
                "total_mileage": "",  # 总里程数 包含智驾和非智驾
                "total_intelligent_driving_mileage": "",  # 智驾总里程数
                "total_intelligent_driving_mileage_change_rate": "",  # 智驾总里程数增减百分比, 正数为增加，负数为减少
                "detail_intelligent_driving": {
                    "acc": "",  # 自适应巡航
                    "ica": "",  # 智能巡航
                    "hnop": "",  # 高速智驾
                    "unop": "",  # 低速智驾
                }
            },
            "preference": {  # 智驾偏好
                "apa_times_rate_rate": "",  # apa激活次数占比
                "apa_activate_vehicle": "",  # apa激活车数量
                "apa_activate_vehicle_change": "",  # apa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "rpa_times_rate_rate": "",  # rpa激活次数占比
                "rpa_activate_vehicle": "",  # rpa激活车数量
                "rpa_activate_vehicle_change": "",  # rpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少
                "vpa_times_rate_rate": "",  # vpa激活次数占比(暂时没有值)
                "vpa_activate_vehicle": "",  # vpa激活车数量(暂时没有值)
                "vpa_activate_vehicle_change": "",  # vpa激活车辆与上一周期对比变化数量，正数为增加，负数为减少(暂时没有值)
            },
            "total_intelligent_driving_vehicles": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times": "",  # 智驾激活总次数，包含所有智驾类型
            "total_intelligent_driving_vehicles_change": "",  # 智驾激活车辆总数，包含所有智驾类型
            "total_intelligent_driving_times_change": "",  # 智驾激活总次数，包含所有智驾类型
            "total_parking_intelligent_vehicles": "",  # 自动泊车激活车辆数
            "total_parking_intelligent_vehicles_change": "",  # 自动泊车激活车辆数与上一周期对比，正数为增加，负数为减少
            "day_times_parking_intelligent": "",  # 自动泊车单车日均激活次数，
            "day_times_parking_intelligent_day_times_change": "",  # 自动泊车单车日均激活次数与上一周期对比，正数为增加，负数为减少
            "apa_duration_parking_duration": "",  # apa_单次泊车平均时长 秒
            "apa_duration_parking_duration_change": "",  # apa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "rpa_duration_parking_duration": "",  # rpa_单次泊车平均时长 秒
            "rpa_duration_parking_duration_change": "",  # rpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            "vpa_duration_parking_duration": "",  # vpa_单次泊车平均时长 秒
            "vpa_duration_parking_duration_change": "",  # vpa_单次泊车平均时长与上一周期对比， 秒，正数为增加，负数为减少
            # 行车功能指标  表数据
            "driving_function_indicators_data": [
                {
                    "function": "",  # 功能名称
                    "vehicles": "",  # 激活车辆数
                    "activate": "",  # 激活次数
                    "avg_mileage": "",  # 单次平均里程
                    "activate_rate": []
                }
            ],
            # 泊车功能指标
            "parking_function_indicators_data": [
                {
                    "function": "",  # 功能名称（VPA，APA，RPA）
                    "avg_duration": "",  # 单次泊车平均时长
                    "activate": "",  # 激活次数
                    "success_rate": "",  # 成功率
                }
            ]
        }
        """
    return Kpi_business.kpi(request,session)


# get_trigger_id_date ==========
@kpiServiceApp.route('/get_trigger_id_date', method=['GET'])
def get_trigger_id_date():
    """
    [GET]/get_trigger_id_date 获取所有TriggerId数据

    Parameters
    ----------
    object: null

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": []
        }
    """
    result = {
        "code": 200,
        "message": "trigger",
        "data": []
    }
    try:
        result_data = (session.execute(text(
            # 这是之前的根据触发次数统计的方式，现在改为直接按照triggerid大小排序
            # """with filter_td as(
            #     select DISTINCT fc.tid from files_completeness fc where fc.comment ='已忽略的trigger'
            #     ),
            #     trigger_count as(
            #      select brd.trigger_id,count(brd.trigger_id) as count_id
            #      from beta_raw_data brd
            #      where   brd.ota_version like '%OTA5%' and brd.trigger_id not in(select ft.tid from filter_td ft)
            #      GROUP by brd.trigger_id
            #      )
            #     select tiim.trigger_id,tiim.total_type,tiim.sub_type,tiim.retrun_describe,tiim.remarks ,
            #     (CASE WHEN tc.count_id is null then 0 else tc.count_id end) as count_id
            #     from trigger_id_info_mngt tiim left join trigger_count tc on tc.trigger_id = tiim.trigger_id
            #     order by tc.count_id desc"""
            f"""select tiim.trigger_id,tiim.total_type,tiim.sub_type,tiim.retrun_describe,tiim.remarks,tiim.level,tiim.is_function,tiim.involve_function
                from trigger_id_info_mngt tiim where tiim.is_ignore = 0 order by tiim.trigger_id asc"""
        )).all())
        # 找到所有的
        for data in result_data:
            result["data"].append([data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7]])
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 版本信息接口 ota_versions ===============
@kpiServiceApp.route('/ota_versions', method=['GET'])
def ota_versions():
    """
    [GET]/ota_versions 版本信息接口

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    default_check_flag:bool 是否仅限发布版本

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": {"ota_version":[]}
        }
    """
    start_date = request.query.start_date
    end_date = request.query.end_date
    default_check_flag = request.query.default_check_flag
    # 返回数据
    result = {
        "code": 200,
        "message": "数据完整性-所有ota",
        "data": {"ota_version":[]}
    }
    try:
        sql_ = f"""
            select 
                max(ovim.ota_ver_stamp),ovim.ota_version
                from ota_version_info_mngt ovim 
                where 
                ovim.date>='{start_date}' and ovim.date<='{end_date}' {'and default_check_flag=1' if default_check_flag == '1' else ''}
                and ((lower(ovim.operate_sys) >='win2.0' and lower(ovim.ota_ver) >= 'ota5') or ovim.ota_ver_stamp = '未知版本')
                group by ovim.ota_version
                order by ovim.ota_version
              """
        result_data = (session.execute(text(sql_)).all())
        # 找到所有的
        for data in result_data:
            result["data"]["ota_version"].append({
                "ota_version_lable":data[0],
                "ota_version": data[1],
            })
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# 车辆列表接口 /vehicle_list =================
@kpiServiceApp.route('/vehicle_list', method=['POST'])
def vehicle_list():
    """
    [POST]/vehicle_list 车辆列表接口

    Parameters
    ----------
    start_date:'2025-01-02'
    end_date:'2025-01-21'
    ota_versions:str 多个

    Returns
    -------
    object
        JSON对象
        {
            message:"",
            code:200,
            "total": 0,
            "data": [{"name": , "vin": }]
        }
    """
    json_params = request.json
    start_date = json_params["start_date"]
    end_date = json_params["end_date"]
    ota_version_list = json_params["ota_versions"]
    ota_version_sql = ""
    if len(ota_version_list) > 0:
        ota_version_sql = f"and brd.ota_version in ({ota_version_list})"
    result = {
        "code": 200,
        "message": "车辆列表",
        "data": []
    }
    parse_data = {}
    try:
        sql_=f"""
            select ifnull(max(vim.vehicle_tested_by),'其他') class_name,max(vim.int_vehicle_no) user_name,max(vim.vin), right(brd.uuid,12) uuid  from beta_raw_data brd 
            left join vehicle_info_mngt vim  on vim.chery_no = brd.uuid
            where 1=1 {ota_version_sql}
            and DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') >= '{start_date}'
            and DATE_FORMAT(FROM_UNIXTIME(brd.data_ts/1000),'%Y-%m-%d') <= '{end_date}'
            group by brd.uuid;
        """
        result_data = ((session.execute(text(sql_))).all())
        for data in result_data:
            if not data[0] in parse_data:
                parse_data[data[0]] = [{"name": data[1] if data[1] else data[3][-12:],
                                        "vin": data[2] if data[2] else data[3][-12:],
                                        "uuid": data[3][-12:],
                                        "level":2,}]
            else:
                parse_data[data[0]].append({"name": data[1] if data[1] else data[3][-12:],
                                            "vin": data[2] if data[2] else data[3][-12:],
                                            "uuid": data[3][-12:],
                                            "level":2,})
        result["data"] = [{'vin':k,'uuid':k,"name": k, "children": v,"level":1,} for k, v in parse_data.items()]
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)


# max_brd_date_temp ==========
@kpiServiceApp.route('/max_brd_date_temp', method=['POST'])
def max_brd_date_temp():
    """
        [POST] /max_brd_date_temp brd表最大时间  （暂时改成KPI表数据的最大时间）
                还需要加x，y轴统计

        Parameters
        ----------
        无

        Returns
        -------
        object
            JSON 数组
              [{
                    date:2025-01-01
                }]
    """
    result = {
        "code": 200,
        "message": "KPI表最大时间",
        "data": ""
    }
    sql_ = f"""
        select max(date_ts)
			  from auto_parking_ver_cont_result ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date": data[0]
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)





# 泊车功能开发


@kpiServiceApp.route('/auto_parking_overview', method=['POST'])
def auto_parking_overview():
    """
        [POST] /auto_parking_overview KPI泊车功能-总览 五个结果数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "col1": 1236,         -- 自动泊车车辆数
                    "col2": 1234593,      -- 自动泊车激活次数
                    "col3": 33.29538835,  -- 自动泊车单车日均
                    "col4": 2.2260,       -- 自动泊车平均换挡
                    "col5": 0.8219        -- 自动泊车成功率
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]

    days = Kpi_business.get2DateDays(start_date, end_date) + 1
    
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    
   


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI泊车功能-总览 五个结果数",
        "data": []
    }
    sql_ = f"""
        select cast(count(distinct vin_id) as char) as col1                                -- 自动泊车车辆数
               ,cast(sum(ct) as char) as col2                                              -- 自动泊车激活次数
               ,cast(round(sum(ct)/{days}/count(distinct vin_id),4) as char) as col3       -- 自动泊车单车日均
               ,cast(round(sum(chgGearCnt_sum)/sum(ct),4) as char) as col4                 -- 自动泊车平均换挡
               ,cast(round(sum(sucs_flag_sum)/sum(ct),4) as char) as col5                  -- 自动泊车成功率
          from auto_parking_overview_result
         where date_ts >= '{start_date}' 
           and date_ts <= '{end_date}'
               {carcodesStr} {otasStr}  ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "col1": data[0],
                    "col2": data[1],
                    "col3": float(data[2]),
                    "col4": float(data[3]),
                    "col5": float(data[4])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print("=================================================================")
        print(start_date,end_date,days)
        print(e)
    return json.dumps(result, ensure_ascii=False)







@kpiServiceApp.route('/auto_parking_by_date', method=['POST'])
def auto_parking_by_date():
    """
        [POST] /auto_parking_by_date KPI泊车功能-总览 按照天，版本汇总触发次数和车辆数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "date_ts": 2025-03-01,
                    "ota_ver_stamp": OTA5-202502231204,
                    "ct": 118,        -- 触发次数
                    "vin_ct": 37      -- 车辆数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI泊车功能-总览 按照天，版本汇总触发次数和车辆数",
        "data": []
    }
    sql_ = f"""
        select date_ts
			   ,ota_ver_stamp
			   ,sum(ct) as ct
			   ,count(distinct vin_id) as vin_ct
		  from (select date_ts
					   ,case when default_check_flag = 1 then ota_ver_stamp
						     else 'Test Version'
				        end as ota_ver_stamp
					   ,ct
					   ,vin_id
					   ,default_check_flag
				  from auto_parking_overview_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 
			   ) t
		 group by date_ts,ota_ver_stamp ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date_ts": data[0],
                    "ota_ver_stamp": data[1],
                    "ct": int(data[2]),
                    "vin_ct": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)





@kpiServiceApp.route('/auto_parking_ver_cont', method=['POST'])
def auto_parking_ver_cont():
    """
        [POST] /auto_parking_ver_cont KPI泊车功能-版本对比 

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "int_model_code": E03,
                    "ota_ver_stamp": OTA6-202504080509,
                    "default_check_flag": No,        
                    "auto_parking_ct": 37 ,      -- 自动泊车次数
                    "apa_ct": 37 ,               -- APA泊车次数
                    "rpa_ct": 37 ,               -- RPA泊车次数
                    "vpa_ct": 37 ,               -- VPA泊车次数
                    "auto_parking_time_avg": 37 ,      -- 平均成功泊车时长
                    "shift_avg_ct": 37 ,               -- 平均换挡次数
                    "max_avg_a": 37 ,                  -- 平均最大加速度
                    "auto_parking_sucs_rate": 37       -- 自动泊车成功率
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI泊车功能-版本对比 ",
        "data": []
    }
    sql_ = f"""
	select int_model_code
		   ,ota_ver_stamp
		   ,default_check_flag
		   ,sum(auto_parking_ct) as auto_parking_ct     -- 自动泊车次数
		   ,sum(apa_ct) as apa_ct                       -- APA泊车次数
		   ,sum(rpa_ct) as rpa_ct                       -- RPA泊车次数
		   ,sum(vpa_ct) as vpa_ct                       -- VPA泊车次数
		   ,round(ifnull(sum(sucs_pk_dur)/sum(sucs_ct)/1000,0),4) as auto_parking_time_avg      -- 平均成功泊车时长
           ,round(ifnull(sum(chgGearCnt)/sum(auto_parking_ct),0),4) as shift_avg_ct             -- 平均换挡次数
           ,round(ifnull(sum(max_lng_acc)/sum(auto_parking_ct),0),4) as max_avg_a               -- 平均最大加速度
           ,round(ifnull(sum(sucs_ct)/sum(auto_parking_ct),0),4) as auto_parking_sucs_rate      -- 自动泊车成功率   
	  from auto_parking_ver_cont_result
	 where date_ts >= '{start_date}'
	   and date_ts <= '{end_date}'
	       {carcodesStr} {otasStr} 
	 group by int_model_code
	          ,ota_ver_stamp
			  ,default_check_flag ;
        """
    print(sql_)
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "int_model_code": data[0],
                    "ota_ver_stamp": data[1],
                    "default_check_flag": data[2],
                    "auto_parking_ct": int(data[3]),
                    "apa_ct": int(data[4]),
                    "rpa_ct": int(data[5]),
                    "vpa_ct": int(data[6]),
                    "auto_parking_time_avg": float(data[7]),
                    "shift_avg_ct": float(data[8]),
                    "max_avg_a": float(data[9]),
                    "auto_parking_sucs_rate": float(data[10])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)







@kpiServiceApp.route('/auto_parking_function', method=['POST'])
def auto_parking_function():
    """
        [POST] /auto_parking_function KPI泊车功能-(APA,RPA,VPA) 上六个结果数据

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA           -- 泊车类型
                    "auto_parking_ct": 5321,     -- 激活次数
                    "vin_ct": 1146,              -- 激活车辆数
                    "vin_avg": 4.6431,           -- 单车日均
                    "shift_avg_ct": 2.2248 ,     -- 平均换挡次数
                    "time_avg": 6.5153 ,         -- 平均泊车时长
                    "sucs_rate": 0.7955          -- 自动泊车成功率
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]

    days = Kpi_business.get2DateDays(start_date, end_date) + 1

    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI泊车功能-(APA,RPA,VPA) 六个结果数据 ",
        "data": []
    }
    sql_ = f"""
    select tid
           ,sum(auto_parking_ct) as auto_parking_ct       -- 激活次数
		   ,count(distinct vin_id)   as vin_ct           -- 激活车辆数
		   ,round(sum(auto_parking_ct)/{days}/count(distinct vin_id),4) as vin_avg  -- 单车日均
           ,round(sum(chgGearCnt)/sum(auto_parking_ct),4) as shift_avg_ct             -- 平均换挡次数
           ,round(sum(pk_dur)/sum(sucs_ct)/1000,4) as time_avg                        -- 平均成功泊车时长
           ,round(sum(sucs_ct)/sum(auto_parking_ct),4) as sucs_rate                   -- 自动泊车成功率   
	  from auto_parking_function_result
	 where date_ts >= '{start_date}'
	   and date_ts <= '{end_date}'
	       {carcodesStr} {otasStr} 
     group by tid ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "auto_parking_ct": int(data[1]),
                    "vin_ct": int(data[2]),
                    "vin_avg": float(data[3]),
                    "shift_avg_ct": float(data[4]),
                    "time_avg": float(data[5]),
                    "sucs_rate": float(data[6])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_parking_a_distri', method=['POST'])
def auto_parking_a_distri():
    """
        [POST] /auto_parking_a_distri KPI泊车功能-加速度分布图

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 
        sucs_code :str
                成功与否 ：Success,Fail

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA                        -- 泊车类型
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "range_floor": 0.1,                       -- 加速度分布范围
                    "ct": 3132                                -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])
    sucscode = str2Array(request.json["sucs_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

    sucsStr = ""
    sucsStrArray = []
    for sucs in sucscode:
        if len(sucs) > 0:
            sucsStrArray.append(sucs)
    if len(sucsStrArray) > 0:
        sucsStr = f" and sucs_fl in ('{"','".join(sucsStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI泊车功能-加速度分布图",
        "data": []
    }
    sql_ = f"""
		select tid
			   ,ota_ver_stamp_2
			   ,range_floor
			   ,count(1) as ct
		  from (select tid
					   ,ota_ver_stamp_2,max_lng_acc
					   ,FLOOR(max_lng_acc * 100) / 100 AS range_floor  -- 向下取整
				  from auto_parking_bar_chart_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {sucsStr} {carcodesStr} {otasStr} 
			   ) t
		 group by tid,ota_ver_stamp_2,range_floor
		 order by tid,ota_ver_stamp_2,range_floor ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "ota_ver_stamp_2": data[1],
                    "range_floor": float(data[2]),
                    "ct": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)





@kpiServiceApp.route('/auto_parking_pk_dur_distri', method=['POST'])
def auto_parking_pk_dur_distri():
    """
        [POST] /auto_parking_pk_dur_distri KPI泊车功能-泊车时长分布图

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 
        sucs_code :str
                成功与否 ：Success,Fail

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA                        -- 泊车类型
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "pk_dur": 3,                              -- 泊车时长分布范围
                    "ct": 3132                                -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])
    sucscode = str2Array(request.json["sucs_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

    sucsStr = ""
    sucsStrArray = []
    for sucs in sucscode:
        if len(sucs) > 0:
            sucsStrArray.append(sucs)
    if len(sucsStrArray) > 0:
        sucsStr = f" and sucs_fl in ('{"','".join(sucsStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI泊车功能-泊车时长分布图",
        "data": []
    }
    sql_ = f"""
		select tid
		       ,ota_ver_stamp_2
			   ,pk_dur
			   ,count(1) as ct
		  from (select tid
					   ,ota_ver_stamp_2,pk_dur/1000
					   ,FLOOR(pk_dur/1000) as pk_dur
				  from auto_parking_bar_chart_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {sucsStr} {carcodesStr} {otasStr} 
			   ) t
		 where pk_dur is not null
		 group by tid,ota_ver_stamp_2,pk_dur
		 order by tid,ota_ver_stamp_2,pk_dur ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "ota_ver_stamp_2": data[1],
                    "pk_dur": int(data[2]),
                    "ct": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)




@kpiServiceApp.route('/auto_parking_chgGearCnt_distri', method=['POST'])
def auto_parking_chgGearCnt_distri():
    """
        [POST] /auto_parking_chgGearCnt_distri KPI泊车功能-换R挡次数分布图

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 
        sucs_code :str
                成功与否 ：Success,Fail

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA                        -- 泊车类型
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "chgGearCnt": 4,                          -- 换R挡分布范围
                    "ct": 3132                                -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])
    sucscode = str2Array(request.json["sucs_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

    sucsStr = ""
    sucsStrArray = []
    for sucs in sucscode:
        if len(sucs) > 0:
            sucsStrArray.append(sucs)
    if len(sucsStrArray) > 0:
        sucsStr = f" and sucs_fl in ('{"','".join(sucsStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI泊车功能-换R挡次数分布图",
        "data": []
    }
    sql_ = f"""
        select tid
			   ,ota_ver_stamp_2
			   ,chgGearCnt
			   ,count(1) as ct
		  from auto_parking_bar_chart_result
		 where date_ts >= '{start_date}'
		   and date_ts <= '{end_date}'
			   {sucsStr} {carcodesStr} {otasStr} 
         group by tid,ota_ver_stamp_2,chgGearCnt
         order by tid,ota_ver_stamp_2,chgGearCnt ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "ota_ver_stamp_2": data[1],
                    "chgGearCnt": int(data[2]),
                    "ct": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_parking_city_distribution', method=['POST'])
def auto_parking_city_distribution():
    """
        [POST] /auto_parking_city_distribution KPI泊车功能-APA,RPA,VPA城市分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 
        sucs_code :str
                成功与否 ：Success,Fail

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA                       -- 泊车类型
                    "city": "苏州市"                          -- 城市
                    "ct": 999                                -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])
    sucscode = str2Array(request.json["sucs_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

    sucsStr = ""
    sucsStrArray = []
    for sucs in sucscode:
        if len(sucs) > 0:
            sucsStrArray.append(sucs)
    if len(sucsStrArray) > 0:
        sucsStr = f" and sucs_fl in ('{"','".join(sucsStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI泊车功能-APA,RPA,VPA城市分布",
        "data": []
    }
    sql_ = f"""
			select tid
                   ,remark03 as city
				   ,count(1) as ct
			  from auto_parking_bar_chart_result
			 where remark03 is not null and remark03 <> ''
			   and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {sucsStr} {carcodesStr} {otasStr} 
			 group by tid,remark03
			 order by tid,remark03 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "city": data[1],
                    "ct": data[2]
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_parking_exit_rea', method=['POST'])
def auto_parking_exit_rea():
    """
        [POST] /auto_parking_exit_rea KPI泊车功能-APA,RPA,VPA退出原因

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 
        sucs_code :str
                成功与否 ：Success,Fail


        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": APA/RPA/VPA                            -- 泊车类型
                    "hmi_code": "120001"                          -- hmi_code
                    "hmi_state": HMI_APA_PARKING_VIEW_FLAT        -- hmi_state
                    "hmi_state_2": VIEW_FLAT                      -- 截取hmi_state
                    "ota_ver_stamp_2": Test Version               -- cat版本和test版本
                    "ct":  1                                      -- 对应次数
                }]
    """

    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])
    sucscode = str2Array(request.json["sucs_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

    sucsStr = ""
    sucsStrArray = []
    for sucs in sucscode:
        if len(sucs) > 0:
            sucsStrArray.append(sucs)
    if len(sucsStrArray) > 0:
        sucsStr = f" and sucs_fl in ('{"','".join(sucsStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI泊车功能-APA,RPA,VPA退出原因",
        "data": []
    }
    sql_ = f"""
		select tid
			   ,hmi_code
			   ,hmi_state
			   ,hmi_state_2
			   ,ota_ver_stamp_2
			   ,ct
		  from (select tid
					   ,hmi_code
					   ,hmi_state
					   ,case when hmi_state like '%HMI_APA_PARKING_%' or hmi_state like '%HMI_VPA_PARKING_%' 
								  then right(hmi_state,length(hmi_state)-16) 
							 when hmi_state like '%HMI_APA_%' or hmi_state like '%HMI_VPA%_' 
								  then right(hmi_state,length(hmi_state)-8) 
							 when hmi_state like '%HMI_PARKING_%'
								  then right(hmi_state,length(hmi_state)-12)     
							 else hmi_state
						end as hmi_state_2
					   ,ota_ver_stamp_2
					   ,sum(ct) as ct
					   ,case when ota_ver_stamp_2 = 'Test Version' then 0 else 1 end as px
				  from auto_exit_reason_result
				 where tid in ('APA','RPA','VPA')
                   and hmi_code not in ('120000','129997','129998')
				   and date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr} 
				 group by tid
						  ,hmi_code
						  ,hmi_state
						  ,ota_ver_stamp_2
			   ) t		  
		 order by tid
		          ,px desc
				  ,ota_ver_stamp_2
				  ,ct desc ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "hmi_code": data[1],
                    "hmi_state": data[2],
                    "hmi_state_2": data[3],
                    "ota_ver_stamp_2": data[4],
                    "ct": int(data[5])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






# 行车功能开发

@kpiServiceApp.route('/auto_driving_overview', method=['POST'])
def auto_driving_overview():
    """
        [POST] /auto_driving_overview KPI行车功能-总览 四个结果数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "col1": 2199,         -- 辅助行车车辆数
                    "col2": 1164551.7,    -- 辅助行车里程数
                    "col3": 727433,       -- 辅助行车激活次数
                    "col4": 1.6009        -- 辅助行车单车日均
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]

    days = Kpi_business.get2DateDays(start_date, end_date) + 1
    
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    
   


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI行车功能-总览 四个结果数",
        "data": []
    }
    sql_ = f"""
		    select count(distinct vin_id) as col1
		  		   ,round(sum(sum_odo),4) as col2
		  		   ,round(sum(ct),4) as col3
		  		   ,round(sum(sum_odo)/sum(ct),4) as col4
		  	  from auto_driving_miles_time_result
		     where date_ts >= '{start_date}'
		  	   and date_ts <= '{end_date}'
		  	       {carcodesStr} {otasStr}   ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "col1": data[0],
                    "col2": data[1],
                    "col3": float(data[2]),
                    "col4": float(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print("=================================================================")
        print(start_date,end_date,days)
        print(e)
    return json.dumps(result, ensure_ascii=False)







@kpiServiceApp.route('/auto_driving_by_date', method=['POST'])
def auto_driving_by_date():
    """
        [POST] /auto_driving_by_date KPI行车功能-总览 按照天，版本汇总触发次数和车辆数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "date_ts": 2025-03-01,
                    "ota_ver_stamp": OTA5-202502231204,
                    "ct": 118,        -- 触发次数
                    "vin_ct": 37      -- 车辆数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI行车功能-总览 按照天，版本汇总触发次数和车辆数",
        "data": []
    }
    sql_ = f"""
			select date_ts
				   ,ota_ver_stamp_2
				   ,sum(ct) as sum_ct
				   ,count(distinct vin_id) as dis_vin_id                   
			  from auto_driving_miles_time_result
			 where date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr}   
			 group by date_ts
					  ,ota_ver_stamp_2  ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date_ts": data[0],
                    "ota_ver_stamp": data[1],
                    "ct": int(data[2]),
                    "vin_ct": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)









@kpiServiceApp.route('/auto_driving_ver_cont', method=['POST'])
def auto_driving_ver_cont():
    """
        [POST] /auto_driving_ver_cont KPI行车功能-版本对比 

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "int_model_code": E03,
                    "ota_ver_stamp": OTA6-202504080509,
                    "default_check_flag": No/Yes,        
                    "tid":ACC,                   
                    "dis_vin_id": 216 ,      -- 激活车辆数
                    "sum_ct": 4942 ,         -- 激活次数
                    "sum_ts": 17.1677 ,      -- 行驶时长(h)
                    "sum_odo": 1076.4 ,      -- 行驶距离(km)
                    "sum_HandsCnt": 464 ,    -- 百公里脱手次数
                    "sum_PresenCnt": 0 ,     -- 百公里离位次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI行车功能-版本对比 ",
        "data": []
    }
    sql_ = f"""
			select int_model_code
			       ,ota_ver_stamp
				   ,default_check_flag
				   ,tid 				   
				   ,count(distinct vin_id) as dis_vin_id
			       ,ifnull(round(sum(ct),4),0) as sum_ct
				   ,ifnull(round(sum(sum_ts)/1000/3600,4),0) as sum_ts
				   ,ifnull(round(sum(sum_odo),4),0) as sum_odo
				   ,ifnull(round(sum(sum_HandsCnt)/sum(sum_odo)*100,2),0) as sum_HandsCnt
				   ,ifnull(round(sum(sum_PresenCnt)/sum(sum_odo)*100,2),0) as sum_PresenCnt
			  from auto_driving_miles_time_result
			 where date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr}   
			 group by int_model_code
					  ,ota_ver_stamp
					  ,default_check_flag
					  ,tid ;
        """
    print(sql_)
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "int_model_code": data[0],
                    "ota_ver_stamp": data[1],
                    "default_check_flag": data[2],
                    "tid": data[3],
                    "dis_vin_id": int(data[4]),
                    "sum_ct": int(data[5]),
                    "sum_ts": float(data[6]),
                    "sum_odo": float(data[7]),
                    "sum_HandsCnt": float(data[8]),
                    "sum_PresenCnt": float(data[9])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)








@kpiServiceApp.route('/auto_driving_by_tid_failure', method=['POST'])
def auto_driving_by_tid_failure():
    """
        [POST] /auto_driving_by_tid_failure KPI行车功能-各功能结果数据-百公里failure状态

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "tid": ,
                    "failure_ct": 
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI行车功能-各功能结果数据-百公里failure状态",
        "data": []
    }
    sql_ = f"""
		select t.tid
			   ,ifnull(round(t.failure/t1.sum_odo*100,2),0) as failure_ct     -- 百公里Failure状态
		  from (select tid
					   ,case when tid='ACC' then sum(acc_failure_ct)
							 when tid='TJA/ICA' then sum(TJA_ICA_failure_ct)
							 when tid='H-NOP' then sum(hnop_failure_ct)
							 when tid='U-NOP' then sum(unop_failure_ct)
						end as failure
				  from auto_driving_failure_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr}   
				 group by tid
			   ) t 
		  left join 
			   (select tid
					   ,ifnull(sum(sum_odo),0) as sum_odo               -- 行驶距离(km)   
				  from auto_driving_miles_time_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr} 			  
				 group by tid 
			   ) t1 
			on t.tid = t1.tid ;
        """
    print(sql_)
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "failure_ct": int(data[1])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)








@kpiServiceApp.route('/auto_driving_by_tid_function', method=['POST'])
def auto_driving_by_tid_function():
    """
        [POST] /auto_driving_by_tid_function KPI行车功能-各功能结果数据

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        Returns
        -------
        object
            JSON 数组
              [{
                    "tid": H-NOP,
                    "vin_ct": 1715 ,          -- 激活车辆数
                    "sum_odo": 623209.9 ,     -- 行驶距离(km)   
                    "sum_ct": 63711 ,         -- 激活次数
                    "avg_odo": 9.7818 ,       -- 辅助行车单次平均里程数
                    "sum_PresenCnt": 0 ,      -- 百公里离位次数
                    "sum_HandsCnt": 266770 ,  -- 百公里脱手次数
                    "avg_dur_time": 0.0       -- LDP单次平均时长(s)
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])    

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


    result = {
        "code": 200,
        "message": "KPI行车功能-各功能结果数据",
        "data": []
    }
    sql_ = f"""
            select tid
			       ,count(distinct vin_id) as vin_ct                         -- 激活车辆数
				   ,ifnull(round(sum(sum_odo),4),0) as sum_odo               -- 行驶距离(km)   
				   ,ifnull(round(sum(ct),4),0) as sum_ct                     -- 激活次数
				   ,ifnull(round(sum(sum_odo)/sum(ct),4),0) as avg_odo       -- 辅助行车单次平均里程数
				   ,ifnull(round(sum(sum_PresenCnt)/sum(sum_odo)*100,2),0) as sum_PresenCnt   -- 百公里离位次数
				   ,ifnull(round(sum(sum_HandsCnt)/sum(sum_odo)*100,2),0) as sum_HandsCnt     -- 百公里脱手次数
				   ,ifnull(round(sum(sum_dur_time)/1000/sum(ct),4),0) as avg_dur_time     -- LDP单次平均时长(s)
			  from auto_driving_miles_time_result
			 where date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 			  
             group by tid ;
        """
    print(sql_)
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "vin_ct": int(data[1]),
                    "sum_odo": float(data[2]),
                    "sum_ct": int(data[3]),
                    "avg_odo": float(data[4]),
                    "sum_PresenCnt": int(data[5]),
                    "sum_HandsCnt": float(data[6]),
                    "avg_dur_time": float(data[7])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)








@kpiServiceApp.route('/auto_driving_day_odo', method=['POST'])
def auto_driving_day_odo():
    """
        [POST] /auto_driving_day_odo KPI行车功能-每日里程分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": H-NOP                              -- 行车类型
                    "date_ts": 2023-01-01,                    -- 日期
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "ct": 23                                  -- 对应里程（km）
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


        
    result = {
        "code": 200,
        "message": "KPI行车功能-每日里程分布",
        "data": []
    }
    sql_ = f"""
            select tid
			       ,date_ts
				   ,ota_ver_stamp_2
				   ,ifnull(round(sum(sum_odo),4),0) as sum_odo               -- 行驶距离(km)   
			  from auto_driving_miles_time_result
			 where date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 			  
             group by tid
			          ,date_ts
					  ,ota_ver_stamp_2
             order by tid
			          ,date_ts
					  ,ota_ver_stamp_2 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "date_ts": data[1],
                    "ota_ver_stamp_2": data[2],
                    "sum_odo": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)








@kpiServiceApp.route('/auto_driving_exit_rea', method=['POST'])
def auto_driving_exit_rea():
    """
        [POST] /auto_driving_exit_rea KPI行车功能-退出原因

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 



        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": acc                                    -- 行车类型
                    "hmi_code": "120001"                          -- hmi_code
                    "hmi_state": HMI_APA_PARKING_VIEW_FLAT        -- hmi_state
                    "hmi_state_2": VIEW_FLAT                      -- 截取hmi_state
                    "ota_ver_stamp_2": Test Version               -- cat版本和test版本
                    "ct":  1                                      -- 对应次数
                }]
    """

    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI行车功能-退出原因",
        "data": []
    }
    sql_ = f"""
		select tid
			   ,hmi_code
			   ,hmi_state
			   ,hmi_state_2
			   ,ota_ver_stamp_2
			   ,ct
		  from (select tid
					   ,hmi_code
					   ,hmi_state
					   ,case when hmi_state like '%HMI_STATE_%'
								  then right(hmi_state,length(hmi_state)-10) 
-- 							 when hmi_state like '%HMI_APA_%' or hmi_state like '%HMI_VPA%_' 
-- 								  then right(hmi_state,length(hmi_state)-8) 
-- 							 when hmi_state like '%HMI_PARKING_%'
-- 								  then right(hmi_state,length(hmi_state)-12)     
							 else hmi_state
						end as hmi_state_2
					   ,ota_ver_stamp_2
					   ,sum(ct) as ct
					   ,case when ota_ver_stamp_2 = 'Test Version' then 0 else 1 end as px
				  from auto_exit_reason_result
				 where tid not in ('APA','RPA','VPA')
                   and hmi_code not in ('120000','129997','129998')
				   and date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr} 
				 group by tid
						  ,hmi_code
						  ,hmi_state
						  ,ota_ver_stamp_2
			   ) t		  
		 order by tid
		          ,px desc
				  ,ota_ver_stamp_2
				  ,ct desc ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "hmi_code": data[1],
                    "hmi_state": data[2],
                    "hmi_state_2": data[3],
                    "ota_ver_stamp_2": data[4],
                    "ct": int(data[5])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_driving_ldp_dur_time', method=['POST'])
def auto_driving_ldp_dur_time():
    """
        [POST] /auto_driving_ldp_dur_time KPI行车功能-LDP持续时长分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "dur_time_2": 1.1,                        -- 持续时间
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "sum_ct": 23                              -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


        
    result = {
        "code": 200,
        "message": "KPI行车功能-LDP持续时长分布",
        "data": []
    }
    sql_ = f"""
			select dur_time_2
			       ,ota_ver_stamp_2		
				   ,ifnull(sum(ct),0) as sum_ct
			  from auto_driving_a_result
			 where tid = 'LDP'
 			   and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 	
			 group by dur_time_2,ota_ver_stamp_2		
             order by dur_time_2,ota_ver_stamp_2 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "dur_time_2": data[0],
                    "ota_ver_stamp_2": data[1],
                    "sum_ct": int(data[2])
                }
            )
        # print('在这里',data_list)
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)







@kpiServiceApp.route('/auto_driving_a_lat', method=['POST'])
def auto_driving_a_lat():
    """
        [POST] /auto_driving_a_lat KPI行车功能-各功能横向加速度和变化率分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "dis_flag": 加速度/变化率  a/a_rate,       -- 加速度/变化率区分标识
                    "tid": ACC                                -- 功能 
                    "max_acc": -0.86                          -- 加速度值
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "sum_ct": 23                              -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


        
    result = {
        "code": 200,
        "message": "KPI行车功能-各功能横向加速度和变化率分布",
        "data": []
    }
    sql_ = f"""
			select 'lat_a' as dis_flag
			       ,tid
			       ,round(max_lat_acc,2) as max_acc
			       ,ota_ver_stamp_2		
				   ,sum(ct) as sum_ct
			  from auto_driving_a_result
			 where max_lat_acc is not null
               and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 	
			 group by tid,max_lat_acc,ota_ver_stamp_2
             union all
			select 'lat_a_rate' as dis_flag
			       ,tid
			       ,max_lat_jerk as max_acc
			       ,ota_ver_stamp_2		
				   ,sum(ct) as sum_ct
			  from auto_driving_a_result
			 where max_lat_jerk is not null
               and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 	
			 group by tid,max_lat_jerk,ota_ver_stamp_2
             order by dis_flag,tid,max_acc,ota_ver_stamp_2 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "dis_flag": data[0],
                    "tid": data[1],
                    "max_acc": float(data[2]),
                    "ota_ver_stamp_2": data[3],
                    "sum_ct": int(data[4])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)







@kpiServiceApp.route('/auto_driving_a_lng', method=['POST'])
def auto_driving_a_lng():
    """
        [POST] /auto_driving_a_lng KPI行车功能-各功能纵向加速度和变化率分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "dis_flag": 加速度/变化率  a/a_rate,       -- 加速度/变化率区分标识
                    "tid": ACC                                -- 功能 
                    "max_acc": -0.86                          -- 加速度值
                    "ota_ver_stamp_2": OTA5-202502231204,     -- 版本2  
                    "sum_ct": 23                              -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])

    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 


        
    result = {
        "code": 200,
        "message": "KPI行车功能-各功能纵向加速度和变化率分布",
        "data": []
    }
    sql_ = f"""
			select 'lng_a' as dis_flag
			       ,tid
			       ,round(max_lng_acc,2) as max_acc
			       ,ota_ver_stamp_2		
				   ,sum(ct) as sum_ct
			  from auto_driving_a_result
			 where max_lng_acc is not null
               and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 	
			 group by tid,max_lng_acc,ota_ver_stamp_2
             union all
			select 'lng_a_rate' as dis_flag
			       ,tid
			       ,max_lng_jerk as max_acc
			       ,ota_ver_stamp_2		
				   ,sum(ct) as sum_ct
			  from auto_driving_a_result
			 where max_lng_jerk is not null
               and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 	
			 group by tid,max_lng_jerk,ota_ver_stamp_2
             order by dis_flag,tid,max_acc,ota_ver_stamp_2 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "dis_flag": data[0],
                    "tid": data[1],
                    "max_acc": float(data[2]),
                    "ota_ver_stamp_2": data[3],
                    "sum_ct": int(data[4])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_driving_city_distribution', method=['POST'])
def auto_driving_city_distribution():
    """
        [POST] /auto_driving_city_distribution KPI行车功能-城市分布

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "tid": ACC                              -- 泊车类型
                    "city": "苏州市"                          -- 城市
                    "ct": 999                                -- 对应次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI行车功能-城市分布",
        "data": []
    }
    sql_ = f"""
			select tid 
			       ,city
				   ,sum(ct) as ct
			  from auto_city_dist_result
			 where city is not null and city <> ''
			   and tid in ('ACC','TJA/ICA','LDP','H-NOP','U-NOP','ELK','Safety Stop')
   			   and date_ts >= '{start_date}'
			   and date_ts <= '{end_date}'
				   {carcodesStr} {otasStr} 
             group by tid 
			          ,city ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "tid": data[0],
                    "city": data[1],
                    "ct": int(data[2])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






@kpiServiceApp.route('/auto_active_safety_overview', method=['POST'])
def auto_active_safety_overview():
    """
        [POST] /auto_active_safety_overview KPI主动安全功能-总览  六个结果数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "sum_ct": 317866                         -- 激活次数
                    "vin_num": 3827                          -- 激活车辆
                    "aeb_ct": 239                            -- AEB激活次数
                    "fcw_ct": 25422                          -- FCW激活次数
                    "elk_ct": 0                              -- ELK激活次数
                    "ldp_ct": 292205                         -- LDP激活次数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI主动安全功能-总览  六个结果数",
        "data": []
    }
    sql_ = f"""
		select sum(ct) as sum_ct                                          -- 激活次数 
		       ,count(distinct vin_id) as vin_num                         -- 激活车辆
			   ,sum(case when tid = 'AEB' then ct else 0 end) as aeb_ct   -- AEB激活次数
			   ,sum(case when tid = 'FCW' then ct else 0 end) as fcw_ct   -- FCW激活次数
			   ,sum(case when tid = 'ELK' then ct else 0 end) as elk_ct   -- ELK激活次数
			   ,sum(case when tid = 'LDP' then ct else 0 end) as ldp_ct   -- LDP激活次数
		  from (select tid
					   ,vin_id
					   ,ct
				  from auto_active_safety_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {otasStr} 
				 union all
				select tid
					   ,vin_id
					   ,ct
				  from auto_driving_miles_time_result
				 where tid in ('LDP','ELK')
				   and date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr}  
			   ) t ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "sum_ct": int(data[0]),
                    "vin_num": int(data[1]),
                    "aeb_ct": int(data[2]),
                    "fcw_ct": int(data[3]),
                    "elk_ct": int(data[4]),
                    "ldp_ct": int(data[5])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)








@kpiServiceApp.route('/auto_active_safety_by_date', method=['POST'])
def auto_active_safety_by_date():
    """
        [POST] /auto_active_safety_by_date KPI主动安全功能-总览  按照天，版本汇总触发次数和车辆数

        Parameters
        ----------
        start_time : str
            开始时间 2025-01-01
        end_time : str
            结束时间 2025-01-20
        ota_versions :str
                ota版本号，逗号隔开  OTA6-202503150411
        car_code :str
                车型 E03,E0Y 

        
        Returns
        -------
        object
            JSON 数组
              [{    
                    "date_ts": 2025-03-01,
                    "ota_ver_stamp_2": OTA5-202502231204,
                    "sum_ct": 118,        -- 激活次数
                    "dis_vin_id": 37      -- 激活车辆数
                }]
    """
 
    start_date = request.json["start_time"]
    end_date = request.json["end_time"]
    otas = str2Array(request.json["ota_versions"])
    carcode = str2Array(request.json["car_code"])


    carcodesStr = ""
    carcodesStrArray = []
    for code in carcode:
        if len(code) > 0:
            carcodesStrArray.append(code)
    if len(carcodesStrArray) > 0:
        carcodesStr = f" and int_model_code in  ('{"','".join(carcodesStrArray)}')"

    otasStr = ""
    otasStrArray = []
    for ota in otas:
        if len(ota) > 0:
            otasStrArray.append(ota)
    if len(otasStrArray) > 0:
        otasStr = f" and ota_ver_stamp in ('{"','".join(otasStrArray)}')" 

        
    result = {
        "code": 200,
        "message": "KPI主动安全功能-总览  按照天，版本汇总触发次数和车辆数",
        "data": []
    }
    sql_ = f"""
		select date_ts
			   ,ota_ver_stamp_2
			   ,sum(ct) as sum_ct                       -- 激活次数 
			   ,count(distinct vin_id) as dis_vin_id    -- 激活车辆数
		  from (select date_ts
		               ,vin_id
					   ,ota_ver_stamp_2
					   ,ct
				  from auto_active_safety_result
				 where date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {otasStr} 
				 union all
				select date_ts
				       ,vin_id
					   ,ota_ver_stamp_2
					   ,ct
				  from auto_driving_miles_time_result
				 where tid in ('LDP','ELK')
				   and date_ts >= '{start_date}'
				   and date_ts <= '{end_date}'
					   {carcodesStr} {otasStr}  
			   ) t
         group by date_ts
		          ,ota_ver_stamp_2 ;
        """
    try:
        result_data = (session.execute(text(sql_)).all())
        data_list = []
        for data in result_data:
            data_list.append(
                {
                    "date_ts": data[0],
                    "ota_ver_stamp_2": data[1],
                    "sum_ct": int(data[2]),
                    "dis_vin_id": int(data[3])
                }
            )
        result["data"] = data_list
        session.commit()
        session.remove()
    except PendingRollbackError:
        session.commit()
        session.remove()
    except Exception as e:
        print(e)
    return json.dumps(result, ensure_ascii=False)






